![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Tuning the Private SQL and PL/SQL AreasA private SQL area is an area in memory that contains binding information and runtime buffers. Every session that issues SQL statements has a private SQL area; reducing these resources can be very effective when large numbers of users are involved. A private SQL area is further segmented into a persistent area and a runtime area. The persistent area contains binding information used during the query for data input and retrieval. The size of this area depends on the number of binds and the number of columns specified in the statement. The runtime area contains information used while the SQL statement is being executed. The size of the runtime area depends on the complexity and type of SQL statements being issued and the size of the rows being processed. The runtime area is freed after the statement has been executed. In a query operation, the runtime area is freed only after all the rows have been fetched. In a dedicated server, the private SQL area is located in the users PGA. In the case of a multithreaded server, the persistent areas and (for SELECT statements) the runtime areas are kept in the SGA. It is important to make sure that each user can allocate enough memory for his or her private SQL area; in the case of the multithreaded server, there must be sufficient space in the SGA to connect the required number of users. Cursors can be used in precompilers to improve performance by reducing the frequency of parsing. To take advantage of cursors, it may be necessary to increase the value of the Oracle parameter OPEN_CURSORS. The size of memory needed for each user is determined by the application and tunables such as OPEN_CURSORS. This memory is allocated automatically. In some operating systems, the amount of memory that can be allocated per user is controlled by system parameters. If your application or tuning changes the amount of memory for each user process, you may have to increase the value of those OS parameters. You may also have to increase the amount of system RAM or reduce the Oracle instance memory usage to avoid swapping or paging. Tuning the Shared PoolTo tune the shared pool, you must look at the individual parts of the shared pool. The shared pool contains both the library cache and the data dictionary cache. In a multithreaded server, the shared pool is also used to store session information. Library Cache The library cache contains the shared SQL and PL/SQL areas. Performance can be improved by both increasing the cache-hit rate in the library cache and by speeding access to the library cache by holding infrequently used SQL statements in cache longer. A cache miss in the shared SQL area occurs when either a parse statement is called and the already parsed statement does not already exist in the shared SQL area or when an application tries to execute an SQL statement and the shared SQL area containing the parsed statement has been deallocated from the library cache. For an SQL statement to take advantage of SQL or PL/SQL statements that may have already been parsed, the following criteria must be met:
At first glance, you may think that many of these conditions make it difficult to take advantage of the shared SQL areas. But users sharing the same application code can easily take advantage of already parsed shared SQL statements. It is to the advantage of the application developer to use the same SQL statements to access the same data and thus ensure that SQL statements within the application can also take advantage of this caching. Using stored procedures whenever possible guarantees that the same shared PL/SQL area is used. Another advantage is that stored procedures are stored in a parsed form, eliminating runtime parsing altogether.
The V$LIBRARYCACHE table contains statistics on how well you are utilizing the library cache. The important columns to view in this table are PINS and RELOADS.
A low number of reloads relative to the number of executions indicates a high cache-hit rate. To get an idea of the total number of cache misses, use this statement: SQL> SELECT SUM(reloads) "Cache Misses", 2 SUM(pins) "Executions", 3 100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent" 4 FROM v$librarycache; Cache Misses Executions Cache Miss Percent ------------ ---------- ------------------ 9 2017 .44620724 The sample output shown here indicates that a total of 2,017 SQL statements, PL/SQL blocks, and object definitions were accessed with only 9 having to reload because they had aged out of the library cache. This means that only .44 percent of these statements resulted in reparsing. To look at the cache hits based on the types of statements, you can use the following statement: SQL> SELECT namespace, 2 reloads "Cache Misses", 3 pins "Executions" 4 FROM v$librarycache; NAMESPACE Cache Misses Executions -------------- ------------ ---------- SQL AREA 4 1676 TABLE/PROCEDURE 5 309 BODY 0 0 TRIGGER 0 0 INDEX 0 21 CLUSTER 0 15 OBJECT 0 0 PIPE 0 0 8 rows selected. The total amount of reloads should be near zero. If you see more than 1 percent library cache misses, take action. You can reduce the number of cache misses by writing identical SQL statements or by increasing the size of the library cache. You should be able to reduce the library cache misses by increasing the amount of memory available for the library cache. This can be done by increasing the Oracle tunable parameter SHARED_POOL_SIZE. You may also have to increase the number of cursors available for a session by increasing the Oracle parameter OPEN_CURSORS.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |